package com.bringway.p2p.dao.impl;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import net.sf.json.JSONObject;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.JhnxmDao;

/**
 * 
 * @author js
 * @version 创建时间：2016年7月4日
 * @功能：计划内项目管理界面
 */
@Repository
public class JhnxmDaoImpl extends P2PJdbcDao implements JhnxmDao {

	Logger logger = Logger.getLogger(JhnxmDaoImpl.class);

	/**
	 * @author js
	 * @param params
	 *            查询债权所有记录
	 * @return
	 */
	@Override
	public List queryJhnzqList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String czqmc = params.optString("czqmc");
		String cssr = params.optString("cssr");
		String clrr = params.optString("clrr");
		String cxmlx = params.optString("cxmlx");
		String cxmzt = params.optString("cxmzt");
		String kyje = params.optString("kyje");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.ID,t1.ZQ_NAME,t1.ZQ_OWNER_XM,t1.ZQ_OWNER_SJHM,t1.ZQ_OWNER_SFZ_NUM,t1.ZQ_DJJE,t1.ZQ_YYJE,t1.ZQ_KYJE");
		sql.append(",date_format(t1.ZQ_START_DATE,'%Y-%m-%d %H:%i:%s') ZQ_START_DATE,date_format(t1.ZQ_END_DATE,'%Y-%m-%d %H:%i:%s') ZQ_END_DATE");
		sql.append(",t1.ZQ_JE,t1.ZQ_TYPE,t1.ZQ_OPER_USER_ID,date_format(t1.ZQ_OPER_DATE,'%Y-%m-%d %H:%i:%s') ZQ_OPER_DATE,t1.ZQ_STATE,t1.ZH_REF_JHN_ZQ_ID");
		sql.append(",max(date_format(date_add(t3.XM_JB_DATE, interval t3.XM_QX_DAY day),'%Y-%m-%d %H:%i:%s')) XM_MAX_END_DATE");
		sql.append(" FROM d_hd_p2p.t_zhfw_jhn_zq t1 left join t_zhfw_jhn_xmzq t2 on t1.ID=t2.REF_JHN_ZQ_ID");
		sql.append(" left join t_zhfw_jhn_xm_cg t3 on t2.REF_JHN_XM_CG_ID=t3.ID where 1=1 ");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(t1.ZQ_START_DATE,'%Y-%m-%d %H:%i:%s') <= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(t1.ZQ_END_DATE,'%Y-%m-%d %H:%i:%s') >= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(czqmc)) {
			sql.append(" AND t1.ZQ_NAME like " + "'%" + czqmc + "%'");
		}
		if (StringUtils.isNotEmpty(cssr)) {
			sql.append(" AND t1.ZQ_OWNER_XM like " + "'%" + cssr + "%'");
		}
		if (StringUtils.isNotEmpty(clrr)) {
			sql.append(" AND t1.ZQ_OPER_USER_ID like " + "'%" + clrr + "%'");
		}
		if (StringUtils.isNotEmpty(cxmlx)) {
			sql.append(" AND t1.ZQ_TYPE =:cxmlx");
			paramMap.put("cxmlx", cxmlx);
		}
		if (StringUtils.isNotEmpty(cxmzt)) {
			sql.append(" AND t1.ZQ_STATE =:cxmzt");
			paramMap.put("cxmzt", cxmzt);
		}
		if (StringUtils.isNotEmpty(kyje)) {
			sql.append(" AND t1.ZQ_KYJE >=:kyje");
			paramMap.put("kyje", kyje);
		}
		sql.append(" group by t1.ID order by t1.ZQ_OPER_DATE desc limit :start,:limit");
		logger.info("queryJhnzqList sql is : " + sql.toString());
		logger.info("queryJhnzqList paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            查询债权记录条数
	 * @return
	 */
	@Override
	public int getJhnzqCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String czqmc = params.optString("czqmc");
		String cssr = params.optString("cssr");
		String clrr = params.optString("clrr");
		String cxmlx = params.optString("cxmlx");
		String cxmzt = params.optString("cxmzt");
		String kyje = params.optString("kyje");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*)");
		sql.append(" from t_zhfw_jhn_zq where 1=1 ");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(ZQ_START_DATE,'%Y-%m-%d %H:%i:%s') <= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(ZQ_END_DATE,'%Y-%m-%d %H:%i:%s') >= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(czqmc)) {
			sql.append(" AND ZQ_NAME like " + "'%" + czqmc + "%'");
		}
		if (StringUtils.isNotEmpty(cssr)) {
			sql.append(" AND ZQ_OWNER_XM like " + "'%" + cssr + "%'");
		}
		if (StringUtils.isNotEmpty(clrr)) {
			sql.append(" AND ZQ_OPER_USER_ID like " + "'%" + clrr + "%'");
		}
		if (StringUtils.isNotEmpty(cxmlx)) {
			sql.append(" AND ZQ_TYPE =:cxmlx");
			paramMap.put("cxmlx", cxmlx);
		}
		if (StringUtils.isNotEmpty(cxmzt)) {
			sql.append(" AND ZQ_STATE =:cxmzt");
			paramMap.put("cxmzt", cxmzt);
		}
		if (StringUtils.isNotEmpty(kyje)) {
			sql.append(" AND ZQ_KYJE >=:kyje");
			paramMap.put("kyje", kyje);
		}
		logger.info("getJhnzqCount sql is : " + sql.toString());
		logger.info("getJhnzqCount paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            插入功能,债权录入
	 * @return
	 */
	@Override
	public String saveJhnzq(JSONObject params) {
		String uuid = getUUId();
		String zqmc = params.optString("zqmc");
		String zqssr = params.optString("zqssr");
		String sjhm = params.optString("sjhm");
		String sfznum = params.optString("sfznum");
		String kssj = params.optString("kssj");
		String jssj = params.optString("jssj");
		String gyh = params.optString("gyh");
		String zqlx = params.optString("zqlx");
		Timestamp kssj_t = null;
		if (StringUtils.isNotEmpty(kssj)) {
			kssj = kssj + " 00:00:00";
			kssj_t = Timestamp.valueOf(kssj);
		}
		Timestamp jssj_t = null;
		if (StringUtils.isNotEmpty(jssj)) {
			jssj = jssj + " 23:59:59";
			jssj_t = Timestamp.valueOf(jssj);
		}
		String zqje = params.optString("zqje");
		StringBuffer sql = new StringBuffer();
		sql.append("insert into t_zhfw_jhn_zq(ID,ZQ_NAME,ZQ_OWNER_XM,ZQ_OWNER_SJHM,ZQ_OWNER_SFZ_NUM");
		sql.append(" ,ZQ_START_DATE,ZQ_END_DATE,ZQ_JE,ZQ_KYJE,ZQ_YYJE,ZQ_DJJE,ZQ_TYPE,ZQ_OPER_USER_ID,ZQ_OPER_DATE)");
		sql.append(" values(:uuid,:zqmc,:zqssr,:sjhm,:sfznum,:kssj_t,:jssj_t,:zqje,:zqje,0,0,:zqlx,:gyh,now())");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("uuid", uuid);
		paramMap.put("zqmc", zqmc);
		paramMap.put("zqssr", zqssr);
		paramMap.put("sjhm", sjhm);
		paramMap.put("sfznum", sfznum);
		paramMap.put("kssj_t", kssj_t);
		paramMap.put("jssj_t", jssj_t);
		paramMap.put("zqje", zqje);
		paramMap.put("zqlx", zqlx);
		paramMap.put("gyh", gyh);
		logger.info("saveJhnzq sql is : " + sql.toString());
		logger.info("saveJhnzq paramMap is : " + paramMap);
		int count=this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
		if (count>0) {
			return uuid;
		}
		return null;
	}

	/**
	 * @author js
	 * @param params
	 *            删除功能,债权录入
	 * @return
	 */
	@Override
	public void deleteJhnzq(JSONObject params) {
		String bizId = params.optString("bizId");
		String sql = "delete from t_zhfw_jhn_zq where id=:bizId";
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("bizId", bizId);
		logger.info("deleteJhnzq sql is " + sql);
		logger.info("deleteJhnzq paramMap is " + paramMap);
		this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            查询功能,根据id
	 * @return
	 */
	@Override
	public Map<String, Object> getJhnzqInfo(JSONObject params) {
		String bizId = params.optString("bizId");
		String fbje = params.optString("fbje");
		StringBuffer sql = new StringBuffer();
		sql.append("select ID,ZQ_NAME,ZQ_OWNER_XM,ZQ_OWNER_SJHM,ZQ_OWNER_SFZ_NUM");
		sql.append(" ,ZQ_START_DATE,ZQ_END_DATE,ZQ_JE,ZQ_KYJE,ZQ_TYPE,ZQ_OPER_USER_ID,ZQ_OPER_DATE,ZQ_STATE");
		sql.append(" from t_zhfw_jhn_zq where id=:bizId");
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("bizId", bizId);
		if (StringUtils.isNotEmpty(fbje)) {
			sql.append(" AND ZQ_KYJE>=:fbje");
			paramMap.put("fbje", fbje);
		}
		logger.info("getJhnzqInfo sql is " + sql.toString());
		logger.info("getJhnzqInfo paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            查询功能,草稿
	 * @return
	 */
	@Override
	public List queryJhncgList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String cxmmc = params.optString("cxmmc");
		String cfbr = params.optString("cfbr");
		String cxmlx = params.optString("cxmlx");
		String cxmzt = params.optString("cxmzt");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select `ID`,`XM_NAME`,`XM_NH_SYL`,`XM_JE`,`XM_QX_DAY`,`XM_QX_MONTH`,`XM_MJQ`,date_format(XM_FB_DATE,'%Y-%m-%d %H:%i:%s') `XM_FB_DATE`");
		sql.append(
				" ,date_format(XM_JB_DATE,'%Y-%m-%d %H:%i:%s') `XM_JB_DATE`,`XM_SQ_USER_ID`,`XM_SQ_USER_SJHM`,date_format(XM_SQRQ,'%Y-%m-%d %H:%i:%s') `XM_SQRQ`,`XM_ZT`,date_format(XM_JX_RQ,'%Y-%m-%d %H:%i:%s') `XM_JX_RQ`");
		sql.append(" ,`XM_SH_USER_ID`,`XM_SH_CONTENT`,date_format(XM_SHRQ,'%Y-%m-%d %H:%i:%s') `XM_SHRQ`,`ORDER`,`XM_TYPE`,XM_XYJB,XM_JHJS,XM_TBFW");
		sql.append(" from t_zhfw_jhn_xm_cg where 1=1 ");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(XM_FB_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(XM_FB_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(cxmmc)) {
			sql.append(" AND XM_NAME like " + "'%" + cxmmc + "%'");
		}
		if (StringUtils.isNotEmpty(cxmlx)) {
			sql.append(" AND `XM_TYPE` =:cxmlx");
			paramMap.put("cxmlx", cxmlx);
		}
		if (StringUtils.isNotEmpty(cxmzt)) {
			sql.append(" AND `XM_ZT` =:cxmzt");
			paramMap.put("cxmzt", cxmzt);
		}
		if (StringUtils.isNotEmpty(cfbr)) {
			sql.append(" AND `XM_SQ_USER_ID` =:cfbr");
			paramMap.put("cfbr", cfbr);
		}
		sql.append(" order by XM_FB_DATE desc limit :start,:limit");
		logger.info("queryJhncgList sql is : " + sql.toString());
		logger.info("queryJhncgList paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            获取记录数,草稿
	 * @return
	 */
	@Override
	public int getJhncgCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String cxmmc = params.optString("cxmmc");
		String cfbr = params.optString("cfbr");
		String cxmlx = params.optString("cxmlx");
		String cxmzt = params.optString("cxmzt");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from t_zhfw_jhn_xm_cg where 1=1 ");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(XM_FB_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(XM_FB_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(cxmmc)) {
			sql.append(" AND XM_NAME like " + "'%" + cxmmc + "%'");
		}
		if (StringUtils.isNotEmpty(cxmlx)) {
			sql.append(" AND `XM_TYPE` =:cxmlx");
			paramMap.put("cxmlx", cxmlx);
		}
		if (StringUtils.isNotEmpty(cxmzt)) {
			sql.append(" AND `XM_ZT` =:cxmzt");
			paramMap.put("cxmzt", cxmzt);
		}
		if (StringUtils.isNotEmpty(cfbr)) {
			sql.append(" AND `XM_SQ_USER_ID` =:cfbr");
			paramMap.put("cfbr", cfbr);
		}
		logger.info("getJhncgCount sql is : " + sql.toString());
		logger.info("getJhncgCount paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            插入功能,草稿
	 * @return
	 */
	@Override
	public String saveJhncg(JSONObject params) {
		String uuid = this.getUUId();
		String xmmc = params.optString("xmmc");
		String xyjb = params.optString("xyjb");
		String tbfw = params.optString("tbfw");
		String jhjs = params.optString("jhjs");
		String nhsyl = params.optString("nhsyl");
		String sjhm = params.optString("sjhm");
		String qx_month = params.optString("qx_month");
		String qx_day = params.optString("qx_day");
		Date now = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-M-dd");
		String fbsj = sdf.format(now).toString() + " 17:00:00";
		logger.info(fbsj);
		Timestamp fbsj_t = Timestamp.valueOf(fbsj);
		String jxrq = params.optString("jxrq");
		Timestamp jxrq_t = null;
		if (StringUtils.isNotEmpty(jxrq)) {
			jxrq = jxrq + " 00:00:00";
			jxrq_t = Timestamp.valueOf(jxrq);
		}
		String jbrq = params.optString("jbrq");
		Timestamp jbrq_t = null;
		if (StringUtils.isNotEmpty(jbrq)) {
			jbrq = jbrq + " 17:00:00";
			jbrq_t = Timestamp.valueOf(jbrq);
		}
		String mjq = params.optString("mjq");
		String xmje = params.optString("xmje");
		String xmlx = params.optString("xmlx");
		String fbr = params.optString("fbr");
		// 状态,申请时间,编号
		int order = getOrderMax() + 1;
		StringBuffer sql = new StringBuffer();
		sql.append("insert into t_zhfw_jhn_xm_cg(ID,XM_NAME,XM_NH_SYL,XM_JE,XM_QX_DAY");
		sql.append(",XM_QX_MONTH,XM_MJQ,XM_FB_DATE,XM_JB_DATE,XM_SQ_USER_ID");
		sql.append(",XM_SQ_USER_SJHM,XM_SQRQ,XM_ZT,XM_JX_RQ,`ORDER`,XM_TYPE,XM_XYJB,XM_JHJS,XM_TBFW)");
		sql.append(" values(:uuid,:xmmc,:nhsyl,:xmje,:qx_day,:qx_month,:mjq,:fbsj_t,:jbrq,:fbr");
		sql.append(",:sjhm,now(),'1',:jxrq,:order,:xmlx,:xyjb,:jhjs,:tbfw)");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("uuid", uuid);
		paramMap.put("xmmc", xmmc);
		paramMap.put("xyjb", xyjb);
		paramMap.put("tbfw", tbfw);
		paramMap.put("jhjs", jhjs);
		paramMap.put("nhsyl", nhsyl);
		paramMap.put("qx_month", qx_month);
		paramMap.put("qx_day", qx_day);
		paramMap.put("jxrq", jxrq_t);
		paramMap.put("jbrq", jbrq_t);
		paramMap.put("fbsj_t", fbsj_t);
		paramMap.put("mjq", mjq);
		paramMap.put("xmje", xmje);
		paramMap.put("xmlx", xmlx);
		paramMap.put("fbr", fbr);
		paramMap.put("sjhm", sjhm);
		paramMap.put("order", order);
		logger.info(" saveJhncg sql is : " + sql.toString());
		logger.info(" saveJhncg paramMap is : " + paramMap);
		int isSuc = this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
		if (isSuc > 0) {
			return uuid;
		}
		return null;
	}

	/**
	 * @author js
	 * @param params
	 *            删除功能,草稿
	 * @return
	 */
	@Override
	public int deleteJhncg(JSONObject params) {
		String bizId = params.optString("bizId");
		String sql = "delete from t_zhfw_jhn_xm_cg where id=:bizId";
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("bizId", bizId);
		logger.info("deleteJhncg sql is " + sql);
		logger.info("deleteJhncg paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            查询功能,根据id,草稿
	 * @return
	 */
	@Override
	public Map<String, Object> getJhncgInfo(JSONObject params) {
		String bizId = params.optString("cgid");
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select `ID`,`XM_NAME`,`XM_NH_SYL`,`XM_JE`,`XM_QX_DAY`,`XM_QX_MONTH`,`XM_MJQ`,`XM_FB_DATE`");
		sql.append(
				" ,`XM_JB_DATE`,`XM_SQ_USER_ID`,`XM_SQ_USER_SJHM`,`XM_SQRQ`,`XM_ZT`,`XM_JX_RQ`");
		sql.append(" ,`XM_SH_USER_ID`,`XM_SH_CONTENT`,`XM_SHRQ`,`ORDER`,`XM_TYPE`,XM_XYJB,XM_JHJS,XM_TBFW");
		sql.append(" from t_zhfw_jhn_xm_cg where ID=:bizId ");
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("bizId", bizId);
		logger.info("getJhncgInfo sql is " + sql.toString());
		logger.info("getJhncgInfo paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            获取天数,根据期限月
	 * @return
	 */
	@Override
	public int getQxDayByQxMonth(String qxMonth) {
		StringBuffer sql = new StringBuffer();
		Map<String, String> paramMap = new HashMap<String, String>();
		sql.append(" select datediff(date_add(now(),interval :QX_MONTH month),now())  QX_DAY FROM DUAL ");
		paramMap.put("QX_MONTH", qxMonth);
		logger.info(" getQxDayByQxMonth sql is : " + sql.toString());
		logger.info(" getQxDayByQxMonth paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	/**
	 * @author js
	 * @param params
	 *            更新项目状态,根据id,债权录入
	 * @return
	 */
	@Override
	public boolean updateJhnzqById(JSONObject params) {
		String zq_id=params.optString("zq_id");
		String zq_zt=params.optString("zq_zt");
		String fbjeString=params.optString("fbje");
		String djjeString=params.optString("djje");
		double djje=0;
		if (StringUtils.isNotEmpty(djjeString)) {
			djje=Double.valueOf(djjeString);
		}
		String yyjeString=params.optString("yyje");
		double yyje=0;
		if (StringUtils.isNotEmpty(yyjeString)) {
			yyje=Double.valueOf(yyjeString);
		}
		double fbje=0;
		if (StringUtils.isNotEmpty(fbjeString)) {
			fbje=Double.valueOf(fbjeString);
		}
		String xmzt=params.optString("xmzt");
		String zq_ref_zqid_in=params.optString("zq_ref_zqid_in");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("zq_id", zq_id);
		sql.append("UPDATE t_zhfw_jhn_zq SET ");
		if (StringUtils.isNotEmpty(zq_zt)&&"00".equals(zq_zt)) {
			if (0==djje+yyje) {
				sql.append(" ZQ_STATE=:zq_zt,ZH_REF_JHN_ZQ_ID=null");
			}else {
				sql.append(" ZQ_DJJE=ZQ_DJJE+:djje,ZQ_YYJE=ZQ_YYJE+:yyje,ZQ_KYJE=ZQ_KYJE-:kyje,ZQ_STATE=:zq_zt");
				paramMap.put("djje", djje);
				paramMap.put("yyje", yyje);
				paramMap.put("kyje", djje+yyje);
			}
			paramMap.put("zq_zt", zq_zt);
		}else if (StringUtils.isNotEmpty(zq_zt)&&"02".equals(zq_zt)){
			sql.append(" ZQ_STATE=:zq_zt,ZH_REF_JHN_ZQ_ID=:zq_ref_zqid_in");
			paramMap.put("zq_ref_zqid_in", zq_ref_zqid_in);
			paramMap.put("zq_zt", zq_zt);
		}else if (StringUtils.isNotEmpty(zq_zt)&&"01".equals(zq_zt)){
			sql.append(" ZQ_STATE=:zq_zt,ZH_REF_JHN_ZQ_ID=:zq_ref_zqid_in");
			paramMap.put("zq_zt", zq_zt);
			paramMap.put("zq_ref_zqid_in", zq_ref_zqid_in);
		}
		
		
		if (fbje>0) {
			if ("21".equals(xmzt)) {
				sql.append(" ZQ_DJJE=ZQ_DJJE-:fbje,ZQ_YYJE=ZQ_YYJE+:fbje");
			} else if ("31".equals(xmzt)) {
				sql.append(" ZQ_DJJE=ZQ_DJJE-:fbje,ZQ_KYJE=ZQ_KYJE+:fbje");
			}else if ("1".equals(xmzt)){
				sql.append(" ZQ_DJJE=ZQ_DJJE+:fbje,ZQ_KYJE=ZQ_KYJE-:fbje");
			}
			paramMap.put("fbje", fbje);
		}
		sql.append(" WHERE ID=:zq_id");
		logger.info("updateJhnzqById sql is " + sql.toString());
		logger.info("updateJhnzqById paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap) > 0;
	}

	/**
	 * @author js
	 * @param params
	 *            插入功能,中间表xmzq
	 * @return
	 */
	@Override
	public boolean saveJhnxmzq(JSONObject params) {
		String id = getUUId();
		String xmcgid=params.optString("xmcgid");
		String zqid=params.optString("zqid");
		String xmid=params.optString("xmid");
		String fbje=params.optString("fbje");
		String is_tqsh=params.optString("is_tqsh");
		String sql = "insert into t_zhfw_jhn_xmzq(ID, REF_JHN_XM_CG_ID, REF_JHN_ZQ_ID, REF_JHN_XM_ID,ZQ_FBJE,IS_TQSH_ZH) values(:id,:xmcgid,:zqid,:xmid,:fbje,:is_tqsh)";
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("id", id);
		paramMap.put("xmcgid", xmcgid);
		paramMap.put("zqid", zqid);
		paramMap.put("xmid", xmid);
		paramMap.put("fbje", fbje);
		paramMap.put("is_tqsh", is_tqsh);
		logger.info("saveJhnxmzq sql is " + sql);
		logger.info("saveJhnxmzq paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap) > 0;
	}

	// 获取草稿表中编号最大值
	private int getOrderMax() {
		// String sql="select max(`order`) from t_zhfw_jhn_xm_cg";
		StringBuffer sql = new StringBuffer();
		sql.append(" select case when max(convert(`order`,signed)) is null then 0 ");
		sql.append(" else max(convert(`order`,signed)) end maxId ");
		sql.append(" from t_zhfw_jhn_xm_cg ");
		Map<String, String> paramMap = new HashMap<>();
		logger.info("getOrderMax sql is " + sql.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	/* 
	 * @方法名  queryJhnxmzqList 
	 * @功能   TODO
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnxmDao#queryJhnxmzqList(net.sf.json.JSONObject)  
	 */
	@Override
	public List queryJhnxmzqList(JSONObject params) {
		String xmcgid = params.optString("bizId");
		String xmid = params.optString("xmid");
		String zqid = params.optString("zqid");
		StringBuffer sql=new StringBuffer();
		Map<String, String> paramMap = new HashMap<>();
		sql.append("select ID,REF_JHN_ZQ_ID ZQID,REF_JHN_XM_CG_ID CGID,REF_JHN_XM_ID XMID,ZQ_FBJE,IS_TQSH_ZH from t_zhfw_jhn_xmzq where 1=1");
		if (StringUtils.isNotEmpty(xmcgid)) {
			sql.append(" AND REF_JHN_XM_CG_ID=:xmcgid");
			paramMap.put("xmcgid", xmcgid);
		}
		if (StringUtils.isNotEmpty(xmid)) {
			sql.append(" AND REF_JHN_XM_ID=:xmid");
			paramMap.put("xmid", xmid);
		}
		if (StringUtils.isNotEmpty(zqid)) {
			sql.append(" AND REF_JHN_ZQ_ID=:zqid");
			paramMap.put("zqid", zqid);
		}
		logger.info("queryJhnxmzqList sql is " + sql.toString());
		logger.info("queryJhnxmzqList paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            删除功能,中间表xmzq
	 * @return
	 */
	@Override
	public int deleteJhnxmzq(JSONObject params) {
		String xmcgid = params.optString("bizId");
		String sql = "delete from t_zhfw_jhn_xmzq where REF_JHN_XM_CG_ID=:xmcgid";
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("xmcgid", xmcgid);
		logger.info("deleteJhnxmzq sql is " + sql);
		logger.info("deleteJhnxmzq paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            查询功能,根据id,债权录入
	 * @return
	 */
	@Override
	public Map<String, Object> queryJhnzqById(String zqid) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("zqid", zqid);
		sql.append("select ID,ZQ_NAME,ZQ_OWNER_XM,ZQ_OWNER_SJHM,ZQ_OWNER_SFZ_NUM,ZQ_DJJE,ZQ_YYJE,ZQ_KYJE");
		sql.append(" ,date_format(ZQ_START_DATE,'%Y-%m-%d %H:%i:%s') ZQ_START_DATE,date_format(ZQ_END_DATE,'%Y-%m-%d %H:%i:%s') ZQ_END_DATE");
		sql.append(" ,ZQ_JE,ZQ_TYPE,ZQ_OPER_USER_ID,date_format(ZQ_OPER_DATE,'%Y-%m-%d %H:%i:%s') ZQ_OPER_DATE,ZQ_STATE");
		sql.append(" from t_zhfw_jhn_zq where ID=:zqid");
		logger.info("queryJhnzqById sql is " + sql);
		logger.info("queryJhnzqById paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            获取记录数,根据cgid,中间表xmzq
	 * @return
	 */
	@Override
	public int getJhncgViewCount(String xmcgid) {
		String sql = "select count(*) from t_zhfw_jhn_xmzq where REF_JHN_XM_CG_ID=:xmcgid";
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("xmcgid", xmcgid);
		logger.info("getJhncgViewCount sql is " + sql);
		logger.info("getJhncgViewCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            更新功能,草稿审核
	 * @return
	 */
	@Override
	public int updateJhncg(JSONObject params) {
		String cgid = params.optString("cgid");
		String shr = params.optString("shr");
		String xmzt = params.optString("xmzt");
		String shbz = params.optString("xmbz");
		StringBuffer sql = new StringBuffer();
		sql.append("update t_zhfw_jhn_xm_cg set XM_SH_USER_ID=:shr,XM_SH_CONTENT=:shbz,XM_SHRQ=now()");
		sql.append(" ,XM_ZT=:xmzt where ID=:cgid");
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("cgid", cgid);
		paramMap.put("shr", shr);
		paramMap.put("xmzt", xmzt);
		paramMap.put("shbz", shbz);
		logger.info("updateJhncg sql is " + sql.toString());
		logger.info("updateJhncg paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	/**
	 * @author js
	 * @param params
	 *            插入功能,审核草稿
	 * @return
	 */
	@Override
	public String saveJhnxm(Map<String, Object> paramMap) {
		if (null != paramMap) {
			StringBuffer sql = new StringBuffer();
			sql.append("insert into t_zhfw_jhn_xm (`ID`,`XM_NAME`,`XM_NH_SYL`,`XM_JE`,`XM_QX_DAY`");
			sql.append(",`XM_QX_MONTH`,`XM_MJQ`,`XM_FB_DATE`,`XM_JB_DATE`,`XM_SQ_USER_ID`");
			sql.append(",`XM_SQ_USER_SJHM`,`XM_SQRQ`,`XM_ZT`,`XM_JX_RQ`,`XM_SH_USER_ID`,`XM_SH_CONTENT`");
			sql.append(",`XM_SHRQ`,`REF_CGID`,`ORDER`,`XM_TYPE`,XM_XYJB,XM_JHJS,XM_TBFW)");
			sql.append(" VALUES(:ID,:XM_NAME,:XM_NH_SYL,:XM_JE,:XM_QX_DAY");
			sql.append(",:XM_QX_MONTH,:XM_MJQ,:XM_FB_DATE,:XM_JB_DATE,:XM_SQ_USER_ID");
			sql.append(",:XM_SQ_USER_SJHM,:XM_SQRQ,:XM_ZT,:XM_JX_RQ,:XM_SH_USER_ID,:XM_SH_CONTENT");
			sql.append(",:XM_SHRQ,:REF_CGID,:ORDER,:XM_TYPE,:XM_XYJB,:XM_JHJS,:XM_TBFW)");
			String id = this.getUUId();
			Object cgid = paramMap.get("ID");
			int order = this.getXmOrderMax() + 1;
			paramMap.put("ID", id);
			paramMap.put("REF_CGID", cgid);
			paramMap.put("ORDER", order);
			logger.info("saveJhnxm sql is " + sql.toString());
			logger.info("saveJhnxm paramMap is " + paramMap);
			this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
			return id;
		}
		return null;
	}

	/**
	 * @author js
	 * @param params
	 *            更新功能,草稿审核,中间表xmzq
	 * @return
	 */
	@Override
	public int updateJhnxmzq(JSONObject params) {
		String set_xmid = params.optString("set_xmid");//
		String where_cgid = params.optString("cgid");//
		String tqshOutId=params.optString("tqshOutId");
		String tqshInId=params.optString("tqshInId");
		StringBuffer sql=new StringBuffer();
		Map<String, String> paramMap = new HashMap<>();
		sql.append("UPDATE t_zhfw_jhn_xmzq SET ");
		//发标审核时更新项目id
		if (StringUtils.isNotEmpty(set_xmid)&&StringUtils.isNotEmpty(where_cgid)) {
			sql.append(" REF_JHN_XM_ID=:set_xmid where REF_JHN_XM_CG_ID=:where_cgid");
			paramMap.put("set_xmid", set_xmid);
			paramMap.put("where_cgid", where_cgid);
		}
		//置换审核成功时
		/*if (StringUtils.isNotEmpty(tqshOutId)&&StringUtils.isNotEmpty(tqshInId)) {
			sql.append(" REF_JHN_ZQ_ID=:tqshInId where REF_JHN_ZQ_ID=:tqshOutId");
			paramMap.put("tqshInId", tqshInId);
			paramMap.put("tqshOutId", tqshOutId);
		}*/
		//置换审核成功时
		if (StringUtils.isNotEmpty(tqshOutId)) {
			sql.append(" IS_TQSH_ZH='01' where REF_JHN_ZQ_ID=:tqshOutId");
			paramMap.put("tqshOutId", tqshOutId);
		}
			
		logger.info("updateJhnxmzq sql is " + sql.toString());
		logger.info("updateJhnxmzq paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	// 获取xm表中编号最大值
	private int getXmOrderMax() {
		// String sql="select max(`order`) from t_zhfw_jhn_xm";
		StringBuffer sql = new StringBuffer();
		sql.append(" select case when max(convert(`order`,signed)) is null then 0 ");
		sql.append(" else max(convert(`order`,signed)) end maxId ");
		sql.append(" from t_zhfw_jhn_xm ");
		Map<String, String> paramMap = new HashMap<>();
		logger.info("getXmOrderMax sql is " + sql.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	/* 
	 * @方法名  queryJhnlbList 
	 * @功能   TODO
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnxmDao#queryJhnlbList(net.sf.json.JSONObject)  
	 */
	@Override
	public List queryJhnlbList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String cxmmc = params.optString("cxmmc");
		String cfbr = params.optString("cfbr");
		String cxmlx = params.optString("cxmlx");
		String cxmzt = params.optString("cxmzt");
		//String zhzt = params.optString("zhzt");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		StringBuffer sql = new StringBuffer();
		sql.append("select T1.`ID`,T1.`XM_NAME`,T1.`XM_NH_SYL`,T1.`XM_JE`,T1.`XM_QX_DAY`,T1.`XM_QX_MONTH`,T1.`XM_MJQ`,date_format(T1.XM_FB_DATE,'%Y-%m-%d %H:%i:%s') `XM_FB_DATE`");
		sql.append(" ,date_format(T1.XM_JB_DATE,'%Y-%m-%d %H:%i:%s') `XM_JB_DATE`,T1.`XM_SQ_USER_ID`,T1.`XM_SQ_USER_SJHM`,date_format(T1.XM_SQRQ,'%Y-%m-%d %H:%i:%s') `XM_SQRQ`,T1.`XM_ZT`,date_format(T1.XM_JX_RQ,'%Y-%m-%d %H:%i:%s') `XM_JX_RQ`");
		sql.append(" ,T1.`XM_JD`,T1.`XM_SH_USER_ID`,T1.`XM_SH_CONTENT`,date_format(T1.XM_SHRQ,'%Y-%m-%d %H:%i:%s') `XM_SHRQ`,T1.`REF_CGID`,T1.`ORDER`,T1.`XM_TYPE`,T1.XM_XYJB,T1.XM_JHJS,T1.XM_TBFW");
		sql.append(",(select sum(tzje) from t_zhfw_jhn_tzrxx where TZZT='成功' and REF_XM_ID=t1.id) XM_MJJE");
		sql.append(" FROM t_zhfw_jhn_xm T1 where T1.XM_ZT>=22 AND T1.XM_ZT<=25");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(T1.XM_FB_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(T1.XM_FB_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(cxmmc)) {
			sql.append(" AND T1.XM_NAME like " + "'%" + cxmmc + "%'");
		}
		if (StringUtils.isNotEmpty(cxmlx)) {
			sql.append(" AND T1.`XM_TYPE` =:cxmlx");
			paramMap.put("cxmlx", cxmlx);
		}
		if (StringUtils.isNotEmpty(cxmzt)) {
			sql.append(" AND T1.`XM_ZT` =:cxmzt");
			paramMap.put("cxmzt", cxmzt);
		}
		if (StringUtils.isNotEmpty(cfbr)) {
			sql.append(" AND T1.`XM_SQ_USER_ID` =:cfbr");
			paramMap.put("cfbr", cfbr);
		}
		sql.append(" order by T1.XM_FB_DATE desc limit :start,:limit");
		logger.info("queryJhnlbList sql is : " + sql.toString());
		logger.info("queryJhnlbList paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	/* 
	 * @方法名  getJhnlbCount 
	 * @功能   TODO
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnxmDao#getJhnlbCount(net.sf.json.JSONObject)  
	 */
	@Override
	public int getJhnlbCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String cxmmc = params.optString("cxmmc");
		String cfbr = params.optString("cfbr");
		String cxmlx = params.optString("cxmlx");
		String cxmzt = params.optString("cxmzt");
		//String zhzt = params.optString("zhzt");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(*) FROM t_zhfw_jhn_xm where XM_ZT>=22 AND XM_ZT<=25");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(XM_FB_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(XM_FB_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(cxmmc)) {
			sql.append(" AND XM_NAME like " + "'%" + cxmmc + "%'");
		}
		if (StringUtils.isNotEmpty(cxmlx)) {
			sql.append(" AND `XM_TYPE` =:cxmlx");
			paramMap.put("cxmlx", cxmlx);
		}
		if (StringUtils.isNotEmpty(cxmzt)) {
			sql.append(" AND `XM_ZT` =:cxmzt");
			paramMap.put("cxmzt", cxmzt);
		}
		if (StringUtils.isNotEmpty(cfbr)) {
			sql.append(" AND `XM_SQ_USER_ID` =:cfbr");
			paramMap.put("cfbr", cfbr);
		}
		logger.info("getJhnlbCount sql is : " + sql.toString());
		logger.info("getJhnlbCount paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	/* 
	 * @方法名  getJhnxmzqViewCount 
	 * @功能   TODO
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnxmDao#getJhnxmzqViewCount(net.sf.json.JSONObject)  
	 */
	@Override
	public int getJhnxmzqViewCount(JSONObject params) {
		String xmcgid = params.optString("bizId");
		String xmid = params.optString("xmid");
		StringBuffer sql=new StringBuffer();
		sql.append("select count(*) from t_zhfw_jhn_xmzq where 1=1");
		if (StringUtils.isNotEmpty(xmcgid)) {
			sql.append(" AND REF_JHN_XM_CG_ID=:xmcgid");
		}
		if (StringUtils.isNotEmpty(xmid)) {
			sql.append(" AND REF_JHN_XM_ID=:xmid");
		}
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("xmcgid", xmcgid);
		paramMap.put("xmid", xmid);
		logger.info("getJhnxmzqViewCount sql is " + sql.toString());
		logger.info("getJhnxmzqViewCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public Map<String, Object> getJhnxmzqInfo(JSONObject params) {
		String id=params.optString("xmzqid");
		String sql="select * from t_zhfw_jhn_xmzq where ID=:id";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("id", id);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql, paramMap);
	}

	@Override
	public int insertJhnzhzqSq(JSONObject params) {
		String id=this.getUUId();
		String zhc_zqid=params.optString("tqshOutId");
		String zhr_zqid=params.optString("tqshInId");
		String sqr=params.optString("zq_tqsh_sqr");
		String sqbz=params.optString("zq_tqsh_sqbz");
		StringBuffer sql=new StringBuffer();
		sql.append("insert into t_zhfw_jhn_zhzq_sq(ID, REF_JHN_ZHC_ZQ_ID, REF_JHN_ZHR_ZQ_ID, ZH_STATE,TQSH_SQ_USER_ID, TQSH_SQ_ZH_DATE, TQSH_SQ_CONTENT)");
		sql.append(" values(:id,:zhc_zqid,:zhr_zqid,'01',:sqr,now(),:sqbz)");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("id",id);
		paramMap.put("zhc_zqid",zhc_zqid);
		paramMap.put("zhr_zqid",zhr_zqid);
		paramMap.put("sqr",sqr);
		paramMap.put("sqbz",sqbz);
		logger.info("insertJhnzhzqSq sql is "+sql.toString());
		logger.info("insertJhnzhzqSq paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	/**
	 * @作者 js  
	 * @方法名 queryJhnzhsqList  
	 * @功能 TODO 
	 * @param params
	 * @return
	 */
	@Override
	public Map<String, Object> queryJhnzhsqList(JSONObject params) {
		String zhc_zqid=params.optString("zhc_zqid");
		String zhr_zqid=params.optString("zhr_zqid");
		String zhsq_id=params.optString("zhsq_id");
		StringBuffer sql=new StringBuffer();
		sql.append("select ZH_STATE,TQSH_SQ_USER_ID,date_format(TQSH_SQ_ZH_DATE,'%Y-%m-%d %H:%i:%s') TQSH_SQ_ZH_DATE");
		sql.append(",TQSH_SQ_CONTENT,TQSH_SH_USER_ID,date_format(TQSH_SH_DATE,'%Y-%m-%d %H:%i:%s') TQSH_SH_DATE");
		sql.append(",TQSH_SH_CONTENT from t_zhfw_jhn_zhzq_sq where 1=1");
		Map<String, String> paramMap =new HashMap<>();
		if (StringUtils.isNotEmpty(zhsq_id)) {
			sql.append(" AND ID=:zhsq_id");
			paramMap.put("zhsq_id", zhsq_id);
		}else if (StringUtils.isNotEmpty(zhc_zqid)&&StringUtils.isNotEmpty(zhr_zqid)) {
			sql.append(" AND REF_JHN_ZHC_ZQ_ID=:zhc_zqid and REF_JHN_ZHR_ZQ_ID=:zhr_zqid and ZH_STATE='01' ");
			paramMap.put("zhc_zqid", zhc_zqid);
			paramMap.put("zhr_zqid", zhr_zqid);
		}
		logger.info("queryJhnzhsqList sql is "+sql.toString());
		logger.info("queryJhnzhsqList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

	@Override
	public int updateJhnzhSq(JSONObject params) {
		String id=params.optString("sqid");
		String shzt=params.optString("shzt");
		String shr=params.optString("tqsh_shr");
		String shbz=params.optString("tqsh_shbz");
		String sql="update t_zhfw_jhn_zhzq_sq set ZH_STATE=:shzt,TQSH_SH_USER_ID=:shr,TQSH_SH_DATE=now(),TQSH_SH_CONTENT=:shbz where ID=:id";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("shzt", shzt);
		paramMap.put("shr", shr);
		paramMap.put("shbz", shbz);
		paramMap.put("id", id);
		logger.info("updateJhnzhSq sql is "+sql);
		logger.info("updateJhnzhSq paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public List<Map<String, Object>> queryJhnzhList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String czqmc = params.optString("czqmc");
		String cssr = params.optString("cssr");
		String clrr = params.optString("clrr");
		String cxmlx = params.optString("cxmlx");
		String cxmzt = params.optString("cxmzt");
		String kyje = params.optString("kyje");
		String zhzt = params.optString("zhzt");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.ID,t1.REF_JHN_XM_ID,t1.REF_JHN_ZHC_ZQ_ID,t1.REF_JHN_ZHR_ZQ_ID,t1.ZH_STATE,t1.TQSH_SQ_USER_ID");
		sql.append(",date_format(t1.TQSH_SQ_ZH_DATE,'%Y-%m-%d %H:%i:%s') TQSH_SQ_ZH_DATE,t1.TQSH_SQ_CONTENT,t1.TQSH_SH_USER_ID");
		sql.append(",date_format(t1.TQSH_SH_DATE,'%Y-%m-%d %H:%i:%s') TQSH_SH_DATE,t1.TQSH_SH_CONTENT");
		sql.append(",t2.ID ZQ_ID,t2.ZQ_NAME,t2.ZQ_OWNER_XM,t2.ZQ_OWNER_SJHM,t2.ZQ_OWNER_SFZ_NUM,t2.ZQ_DJJE,t2.ZQ_YYJE,t2.ZQ_KYJE");
		sql.append(",date_format(t2.ZQ_START_DATE,'%Y-%m-%d %H:%i:%s') ZQ_START_DATE,date_format(t2.ZQ_END_DATE,'%Y-%m-%d %H:%i:%s') ZQ_END_DATE");
		sql.append(",t2.ZQ_JE,t2.ZQ_TYPE,t2.ZQ_OPER_USER_ID,date_format(t2.ZQ_OPER_DATE,'%Y-%m-%d %H:%i:%s') ZQ_OPER_DATE,t2.ZQ_STATE");
		sql.append(" from t_zhfw_jhn_zhzq_sq t1 left join t_zhfw_jhn_zq t2 on t1.REF_JHN_ZHC_ZQ_ID=t2.id where 1=1 ");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(t1.TQSH_SQ_ZH_DATE,'%Y-%m-%d %H:%i:%s') <= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(t1.TQSH_SQ_ZH_DATE,'%Y-%m-%d %H:%i:%s') >= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(czqmc)) {
			sql.append(" AND t2.ZQ_NAME like " + "'%" + czqmc + "%'");
		}
		if (StringUtils.isNotEmpty(cssr)) {
			sql.append(" AND t2.ZQ_OWNER_XM like " + "'%" + cssr + "%'");
		}
		if (StringUtils.isNotEmpty(clrr)) {
			sql.append(" AND t2.ZQ_OPER_USER_ID like " + "'%" + clrr + "%'");
		}
		if (StringUtils.isNotEmpty(cxmlx)) {
			sql.append(" AND t2.ZQ_TYPE =:cxmlx");
			paramMap.put("cxmlx", cxmlx);
		}
		if (StringUtils.isNotEmpty(cxmzt)) {
			sql.append(" AND t2.ZQ_STATE =:cxmzt");
			paramMap.put("cxmzt", cxmzt);
		}
		if (StringUtils.isNotEmpty(kyje)) {
			sql.append(" AND t2.ZQ_KYJE >=:kyje");
			paramMap.put("kyje", kyje);
		}
		if (StringUtils.isNotEmpty(zhzt)) {
			sql.append(" AND t1.ZH_STATE =:zhzt");
			paramMap.put("zhzt", zhzt);
		}
		sql.append(" order by t1.TQSH_SQ_ZH_DATE desc limit :start,:limit");
		logger.info("queryJhnzhList sql is : " + sql.toString());
		logger.info("queryJhnzhList paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getJhnzhCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String czqmc = params.optString("czqmc");
		String cssr = params.optString("cssr");
		String clrr = params.optString("clrr");
		String cxmlx = params.optString("cxmlx");
		String cxmzt = params.optString("cxmzt");
		String kyje = params.optString("kyje");
		String zhzt = params.optString("zhzt");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*)");
		sql.append(" from t_zhfw_jhn_zhzq_sq t1 left join t_zhfw_jhn_zq t2 on t1.REF_JHN_ZHC_ZQ_ID=t2.id where 1=1 ");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(t1.TQSH_SQ_ZH_DATE,'%Y-%m-%d %H:%i:%s') <= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(t1.TQSH_SQ_ZH_DATE,'%Y-%m-%d %H:%i:%s') >= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(czqmc)) {
			sql.append(" AND t2.ZQ_NAME like " + "'%" + czqmc + "%'");
		}
		if (StringUtils.isNotEmpty(cssr)) {
			sql.append(" AND t2.ZQ_OWNER_XM like " + "'%" + cssr + "%'");
		}
		if (StringUtils.isNotEmpty(clrr)) {
			sql.append(" AND t2.ZQ_OPER_USER_ID like " + "'%" + clrr + "%'");
		}
		if (StringUtils.isNotEmpty(cxmlx)) {
			sql.append(" AND t2.ZQ_TYPE =:cxmlx");
			paramMap.put("cxmlx", cxmlx);
		}
		if (StringUtils.isNotEmpty(cxmzt)) {
			sql.append(" AND t2.ZQ_STATE =:cxmzt");
			paramMap.put("cxmzt", cxmzt);
		}
		if (StringUtils.isNotEmpty(kyje)) {
			sql.append(" AND t2.ZQ_KYJE >=:kyje");
			paramMap.put("kyje", kyje);
		}
		if (StringUtils.isNotEmpty(zhzt)) {
			sql.append(" AND t1.ZH_STATE =:zhzt");
			paramMap.put("zhzt", zhzt);
		}
		logger.info("getJhnzhCount sql is : " + sql.toString());
		logger.info("getJhnzhCount paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> queryTjrsjhmList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String sjhm=params.optString("sjhm");
		StringBuffer sql=new StringBuffer();
		sql.append("select t1.SJHM,t2.REAL_NAME,t2.SFZ_NUM from t_zhfw_user t1 ");
		sql.append(" left join t_zhfw_smrz t2 on t1.USER_NAME=t2.USER_NAME where SJHM like :SJHM LIMIT :START,:LIMIT");
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("SJHM", sjhm+"%");
		logger.info("queryTjrsjhmList sql is : " + sql.toString());
		logger.info("queryTjrsjhmList paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}
}
